﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DAO;
using Excel = Microsoft.Office.Interop.Excel;
namespace GUI
{
    public partial class ThongKeSachTraTre : UserControl
    {
        public ThongKeSachTraTre()
        {
            InitializeComponent();
            conn = new SqlConnection(SqlDataAccessHelper.ReadConnectionString("ConnectionString.xml"));
        }
        SqlConnection conn;
        DateTime ngaybaocao = new DateTime();
        private DataTable GetSourceLan1()
        {
            DataTable dt = new DataTable();
            try
            {
                SqlCommand comm = conn.CreateCommand();
                comm.CommandType = CommandType.StoredProcedure;
                comm.Parameters.AddWithValue("@ngay", ngaybaocao);
                comm.CommandText = "TimSachTraTreTrongNgay";
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = comm;
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {

            }
            return dt;
        }
        private void GetSourceLan2(DataTable dt)
        {
            // Su dung source tu getsourcelan1 de lam viec
            int stt = 0;
            int ngaytre = 0;
            String tensach = String.Empty;
            String matailieu = String.Empty;
            DateTime ngaymuon = new DateTime();
            SqlParameter outparam = new SqlParameter();
            //------------------------------------------------------
            SqlCommand comm = conn.CreateCommand();
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "TimNgayMuonCuaTaiLieu";
            comm.UpdatedRowSource = UpdateRowSource.OutputParameters;

            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    //-----------------------------------------------------
                    comm.Parameters.Clear();
                    //-----------------------------------------------------
                    Int32.TryParse(dr["SoNgayTre"].ToString(), out ngaytre);
                    tensach = dr["TenTuaDe"].ToString();
                    stt++;
                    //-Xach cai ma tai lieu ra de kiem ngay muon-----------
                    matailieu = dr["MaTaiLieu"].ToString();
                    comm.Parameters.AddWithValue("@MaTaiLieu", matailieu);
                    outparam.DbType = DbType.DateTime;
                    outparam.Direction = ParameterDirection.Output;
                    outparam.ParameterName = "@ngaymuon";
                    comm.Parameters.Add(outparam);
                    comm.ExecuteNonQuery();
                    ngaymuon = DateTime.Parse(outparam.Value.ToString());
                }
                catch (Exception ex)
                {

                }
                finally
                {
                    //------------------------------------------------------
                    object[] row = { stt, tensach, ngaymuon, ngaytre };
                    dgvReport.Rows.Add(row);
                }
            }
        }

        private void dTimeNgay_ValueChanged(object sender, EventArgs e)
        {
            ngaybaocao = dTimeNgay.Value;
            conn.Open();
            GetSourceLan2(GetSourceLan1());
            conn.Close();
        }

        private void buttonX1_Click(object sender, EventArgs e)
        {
            xuatFile();
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (System.Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception occured while releasing object: " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
        private void xuatFile()
        {
            SaveFileDialog save = new SaveFileDialog();
            save.Filter = "Excel file *.xls|*.xls";
            if (save.ShowDialog() == DialogResult.OK)
            {
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                Excel.Application xlApp;
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                object misValue = System.Reflection.Missing.Value;

                xlApp = new Excel.ApplicationClass();
                xlWorkBook = xlApp.Workbooks.Add(misValue);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                int i;
                int j;
                xlWorkSheet.Cells[1, 1] = labelX2.Text;
                xlWorkSheet.Cells[2, 1] = "NGÀY: ";
                xlWorkSheet.Cells[2, 2] = dTimeNgay.Value.Day + "-" + dTimeNgay.Value.Month + "-" + dTimeNgay.Value.Year;
                xlWorkSheet.Cells[3, 1] = "NGÀY TẠO: ";
                xlWorkSheet.Cells[3, 2] = DateTime.Now;
                for (i = 5; i <= dgvReport.RowCount + 4; i++)
                {
                    for (j = 0; j <= dgvReport.ColumnCount - 1; j++)
                    {
                        DataGridViewCell cell = dgvReport[j, i - 5];
                        xlWorkSheet.Cells[5, j + 1] = dgvReport.Columns[j].HeaderText;
                        xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                    }
                }
                xlWorkBook.SaveAs(save.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                xlWorkBook.Close(true, misValue, misValue);
                xlApp.Quit();

                releaseObject(xlWorkSheet);
                releaseObject(xlWorkBook);
                releaseObject(xlApp);

                MessageBox.Show("Excel file created");
                System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            }
        }
    }
}
